package com.wap;

import java.util.Random;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Date;
import java.util.Hashtable;

import com.base.Constant;
import com.base.DBPool;
import com.base.DatabaseDAO;
import com.base.DatabaseObject;
import com.utils.StringTool;

public class DBContentDAO extends DatabaseDAO {

	public ArrayList<DatabaseObject> getGameCategory(String parentIds,
			String ignoreIdList) {
		ArrayList<DatabaseObject> gameCategoryList = null;

		if (StringTool.isEmptyOrNul(ignoreIdList)) { 
			// Khong loai bo ID nao
			gameCategoryList = findByDynamicWhere(Constant.DB_POOL_MWAP,
					"CATEGORY", new String[] { "ID", "PARENT_ID", "CODE",
							"NAME", "DESCRIPTION", "STATUS", "CREATED",
							"MODIFIED", "ALIAS" }, new String[] { null, null,
							null, null, null, null, null, null, null },
					"status=? and PARENT_ID in (" + parentIds + ")",
					new Object[] { Integer.valueOf(1) }, "NAME");
		} else {
			gameCategoryList = findByDynamicWhere(Constant.DB_POOL_MWAP,
					"CATEGORY", new String[] { "ID", "PARENT_ID", "CODE",
							"NAME", "DESCRIPTION", "STATUS", "CREATED",
							"MODIFIED", "ALIAS" }, new String[] { null, null,
							null, null, null, null, null, null, null },
					"status=? and PARENT_ID in (" + parentIds
							+ ") and ID not in (" + ignoreIdList + ")",
					new Object[] { Integer.valueOf(1) }, "NAME");
		}
		return gameCategoryList;
	}

	public DatabaseObject getGameCategory(BigDecimal catId) {
		ArrayList<DatabaseObject> cats = findByDynamicWhere(
				Constant.DB_POOL_MWAP, "CATEGORY", new String[] { "ID",
						"PARENT_ID", "CODE", "NAME", "DESCRIPTION", "STATUS",
						"CREATED", "MODIFIED", "ALIAS" }, new String[] { null,
						null, null, null, null, null, null, null, null },
				"id=?", new Object[] { catId }, null);
		if (cats != null && !cats.isEmpty())
			return cats.get(0);
		return null;
		
	}

	public int countGameByCategory(BigDecimal catId) {
		String strSql = null;
		Connection conn = null;
		PreparedStatement preStmt = null;
		ResultSet rs = null;
		int result = 0;
		try {
			conn = DBPool.getConnection(Constant.DB_POOL_MWAP);
			strSql = "select count(*) from vnt_media_content ctent"
					+ "	left join CONTENT_CATEGORY_MAP ccat on ctent.id=ccat.content_id"
					+ "	where ccat.category_id=? and status=1"
					+ "	order by ccat.priority";
			// prepare statement
			preStmt = conn.prepareStatement(strSql);
			preStmt.setBigDecimal(1, catId);

			rs = preStmt.executeQuery();
			if (rs.next()) {
				result = rs.getInt(1);
			}

		} catch (Exception _e) {
			logger.error("countGameByCategory: Error executing " + strSql
					+ " >>> " + _e.toString());
		} finally {
			DBPool.releaseConnection(Constant.DB_POOL_MWAP, conn, preStmt, rs);
		}
		return result;

	}

	public ArrayList<DatabaseObject> getGameListByCategory(BigDecimal catId,
			int page, int rowsPerPage) {
		String strSql = null;
		Connection conn = null;
		PreparedStatement preStmt = null;
		ResultSet rs = null;
		ArrayList<DatabaseObject> result = null;
		try {
			conn = DBPool.getConnection(Constant.DB_POOL_MWAP);
			int startRec = (page - 1) * rowsPerPage + 1;
			int endRec = page * rowsPerPage;
			strSql = "select * from ("
					+ "	select ccat.priority, ccat.category_id, ctent.id, ctent.name, ctent.SUMMARIZE, ctent.PREVIEW_IMAGE, ctent.money, rownum as r from vnt_media_content ctent"
					+ "	left join CONTENT_CATEGORY_MAP ccat on ctent.id=ccat.content_id"
					+ "	where ccat.category_id=? and status=1"
					+ "	order by ccat.priority) where r between " + startRec
					+ " and " + endRec;
			// prepare statement
			preStmt = conn.prepareStatement(strSql);
			preStmt.setBigDecimal(1, catId);

			rs = preStmt.executeQuery();
			result = new ArrayList<DatabaseObject>();
			while (rs.next()) {
				DatabaseObject object = new DatabaseObject();
				Hashtable<String, Object> attribute = new Hashtable<String, Object>();
				attribute.put("category_id", rs.getObject(2) == null ? "" : rs
						.getObject(2));
				attribute.put("id", rs.getObject(3) == null ? "" : rs
						.getObject(3));
				attribute.put("name", rs.getObject(4) == null ? "" : rs
						.getObject(4));
				attribute.put("summary", rs.getObject(5) == null ? "" : rs
						.getObject(5));
				attribute.put("preview_image", rs.getObject(6) == null ? ""
						: rs.getObject(6));
				attribute.put("money", rs.getObject(7) == null ? "" : rs
						.getObject(7));
				object.setAttribute(attribute);
				result.add(object);
			}

		} catch (Exception _e) {
			logger.error("getGameListByCategory: Error executing " + strSql
					+ " >>> " + _e.toString());
		} finally {
			DBPool.releaseConnection(Constant.DB_POOL_MWAP, conn, preStmt, rs);
		}
		return result;

	}

	public DatabaseObject getGameContent(BigDecimal id) {
		ArrayList<DatabaseObject> games = findByDynamicWhere(
				Constant.DB_POOL_MWAP,"vnt_media_content",
				new String[] { "id",
						"type_id", "url", "name", "summarize", "detail",
						"preview_image", "avatar_image", "avatar_image_w",
						"avatar_image_h", "status", "code", "money" },
				new String[] { null, null, null, null, null, null, null, null,
						null, null, null, null, null }, "id=?",
				new Object[] { id }, null);
		if (games != null && !games.isEmpty())
			return games.get(0);
		return null;
	}
	
	public DatabaseObject getWeatherForecasts(BigDecimal id) {
		ArrayList<DatabaseObject> games = findByDynamicWhere(
				Constant.DB_POOL_MWAP, "weather_forecasts", new String[] { "id",
						"code", "alias", "name", "content", "created","modified"},
				new String[] { null, null, null, null, null, null, null, null,
						null, null, null, null, null }, "id!=0",
				new Object[] { id }, null);
		if (games != null && !games.isEmpty())
			return games.get(0);
		return null;
	}
	// ======================================
	public ArrayList<DatabaseObject> getCategory(String parentIds,
			String ignoreIdList) {
		ArrayList<DatabaseObject> gameCategoryList = null;

		if (StringTool.isEmptyOrNul(ignoreIdList)) { // Khong loai bo ID nao
			gameCategoryList = findByDynamicWhere(Constant.DB_POOL_MWAP,
					"CATEGORY", new String[] { "ID", "PARENT_ID", "CODE",
							"NAME", "DESCRIPTION", "STATUS", "CREATED",
							"MODIFIED", "ALIAS" }, new String[] { null, null,
							null, null, null, null, null, null, null },
					"status=? and ID not in ('1382') and PARENT_ID in (" + parentIds + ")",
					new Object[] { Integer.valueOf(1) }, "NAME");
		} else {
			gameCategoryList = findByDynamicWhere(Constant.DB_POOL_MWAP,
					"CATEGORY", new String[] { "ID", "PARENT_ID", "CODE",
							"NAME", "DESCRIPTION", "STATUS", "CREATED",
							"MODIFIED", "ALIAS" }, new String[] { null, null,
							null, null, null, null, null, null, null },
					"status=? and PARENT_ID in (" + parentIds
							+ ") and ID not in (" + ignoreIdList + ")",
					new Object[] { Integer.valueOf(1) }, "NAME");
		}
		return gameCategoryList;
	}

	public ArrayList<DatabaseObject> getContentGroup(String parentIds, 
			String ignoreIdList) {
		ArrayList<DatabaseObject> gameCategoryList = null;

		if (StringTool.isEmptyOrNul(ignoreIdList)) { // Khong loai bo ID nao
			gameCategoryList = findByDynamicWhereContentGroup(Constant.DB_POOL_MWAP,
					"CONTENT_GROUP", new String[] { "CONTENT_GROUP_ID", "PARENT_ID", "CODE",
							"ALIAS", "NAME", "CREATED", "MODIFIED"}, 
							new String[] { null, null,
							null, null, null, null, null },
					"PARENT_ID in (" + parentIds+ ")", "NAME");
		} else {
			gameCategoryList = findByDynamicWhereContentGroup(Constant.DB_POOL_MWAP,
					"CONTENT_GROUP", new String[] { "CONTENT_GROUP_ID", "PARENT_ID", "CODE",
							"ALIAS", "NAME", "CREATED", "MODIFIED"},
							new String[] { null, null,
							null, null, null, null, null },
					"PARENT_ID in (" + parentIds+ ") and CONTENT_GROUP_ID not in (" + ignoreIdList + ")", "NAME");
		}
		return gameCategoryList;
	}

	public DatabaseObject getCategory(BigDecimal catId) {
		ArrayList<DatabaseObject> cats = findByDynamicWhere(
				Constant.DB_POOL_MWAP, "CATEGORY", new String[] { "ID",
						"PARENT_ID", "CODE", "NAME", "DESCRIPTION", "STATUS",
						"CREATED", "MODIFIED", "ALIAS" }, new String[] { null,
						null, null, null, null, null, null, null, null },
				"id=?", new Object[] { catId }, null);
		if (cats != null && !cats.isEmpty())
			return cats.get(0);
		return null;
	}
	public DatabaseObject getContentGroup(BigDecimal catId) {
		ArrayList<DatabaseObject> cats = findByDynamicWhere(
				Constant.DB_POOL_MWAP, "CONTENT_GROUP", new String[] { "CONTENT_GROUP_ID",
						"PARENT_ID", "CODE", "ALIAS", "NAME",
						"CREATED", "MODIFIED"}, new String[] { null,
						null, null, null, null, null, null},
				"content_group_id=?", new Object[] { catId }, null);
		if (cats != null && !cats.isEmpty())
			return cats.get(0);
		return null;
	}
	public DatabaseObject getCategoryContent(BigDecimal catId) {
		ArrayList<DatabaseObject> cats = findByDynamicWhere(
				Constant.DB_POOL_MWAP, "content_category_map", new String[] { "CONTENT_GROUP_ID",
						"PARENT_ID", "CODE", "ALIAS","NAME","CREATED","MODIFIED"},
						new String[] { null,null, null, null,
						null, null, null},
				"content_group_id=?", new Object[] { catId }, null);
		if (cats != null && !cats.isEmpty())
			return cats.get(0);
		return null;
	}
	public DatabaseObject getLotteryBrand(BigDecimal sId) {
		ArrayList<DatabaseObject> cats = findByDynamicWhere(
				Constant.DB_POOL_MWAP, "lottery_result", new String[] { "lottery_result_id",
						"lottery_brand_id", "lottery_type_id", "g_s_id","draw_time","value","created","modified"},
						new String[] { null,null, null, null,null,
						null, null, null},
				"lottery_result_id=?", new Object[] { sId }, null);
		if (cats != null && !cats.isEmpty())
			return cats.get(0);
		return null;
	}
	public int countContentByCategory(BigDecimal catId) {
		String strSql = null;
		Connection conn = null;
		PreparedStatement preStmt = null;
		ResultSet rs = null;
		int result = 0;
		try {
			conn = DBPool.getConnection(Constant.DB_POOL_MWAP);
			strSql = "select count(*) from vnt_media_content ctent"
					+ "	left join CONTENT_CATEGORY_MAP ccat on ctent.id=ccat.content_id"
					+ "	where ccat.category_id=? and status=1"
					+ "	order by ccat.priority";
			// prepare statement
			preStmt = conn.prepareStatement(strSql);
			preStmt.setBigDecimal(1, catId);

			rs = preStmt.executeQuery();
			if (rs.next()) {
				result = rs.getInt(1);
			}

		} catch (Exception _e) {
			logger.error("countContentByCategory: Error executing " + strSql
					+ " >>> " + _e.toString());
		} finally {
			DBPool.releaseConnection(Constant.DB_POOL_MWAP, conn, preStmt, rs);
		}
		return result;
	}
	public int countContentBy(BigDecimal sId) {
		String strSql = null;
		Connection conn = null;
		PreparedStatement preStmt = null;
		ResultSet rs = null;
		int result = 0;
		try {
			conn = DBPool.getConnection(Constant.DB_POOL_MWAP);
			strSql = "select count(*) from weather_forecasts weat"
					+ "	order by weat.name";
			// prepare statement
			preStmt = conn.prepareStatement(strSql);
			preStmt.setBigDecimal(1, sId);

			rs = preStmt.executeQuery();
			if (rs.next()) {
				result = rs.getInt(1);
			}

		} catch (Exception _e) {
			logger.error("countContentByCategory: Error executing " + strSql
					+ " >>> " + _e.toString());
		} finally {
			DBPool.releaseConnection(Constant.DB_POOL_MWAP, conn, preStmt, rs);
		}
		return result;
	}
	public int countContentByCategoryGroup(BigDecimal catId) {
		String strSql = null;
		Connection conn = null;
		PreparedStatement preStmt = null;
		ResultSet rs = null;
		int result = 0;
		try {
			conn = DBPool.getConnection(Constant.DB_POOL_MWAP);
			strSql = "select count(*) from content ctent"
					+ "	left join cnt_cntg_map ccat on ctent.content_id=ccat.content_id"
					+ "	where ccat.content_group_id=?";
			// prepare statement
			preStmt = conn.prepareStatement(strSql);
			preStmt.setBigDecimal(1, catId);

			rs = preStmt.executeQuery();
			if (rs.next()) {
				result = rs.getInt(1);
			}

		} catch (Exception _e) {
			logger.error("countContentByCategoryGroup: Error executing " + strSql
					+ " >>> " + _e.toString());
		} finally {
			DBPool.releaseConnection(Constant.DB_POOL_MWAP, conn, preStmt, rs);
		}
		return result;
	}
	public ArrayList<DatabaseObject> getContentListByCategory(BigDecimal catId,
			int page, int rowsPerPage) {
		String strSql = null;
		Connection conn = null;
		PreparedStatement preStmt = null;
		ResultSet rs = null;
		ArrayList<DatabaseObject> result = null;
		try {
			conn = DBPool.getConnection(Constant.DB_POOL_MWAP);
			int startRec = (page - 1) * rowsPerPage + 1;
			int endRec = page * rowsPerPage;
			strSql = " SELECT * FROM ( " 
					+ " SELECT priority, category_id, ID, NAME, summarize, preview_image,"
					+ " code, avatar_image,money, ROWNUM AS r "
					+ "	FROM (SELECT   a.priority, b.category_id, a.ID, a.NAME, a.summarize,"
					+ "	a.preview_image, a.code, a.avatar_image, a.money"
					+ "	FROM vnt_media_content a, content_category_map b"
					+ " WHERE a.ID = b.content_id(+) AND b.category_id =? AND a.status = 1"
					+ " ORDER BY a.priority ASC, a.created DESC))"
					+ " WHERE r between " + startRec + " and " + endRec;

			// prepare statement
			preStmt = conn.prepareStatement(strSql);
			preStmt.setBigDecimal(1, catId);
			System.out.println(catId);

			rs = preStmt.executeQuery();
			System.out.println(strSql);
			result = new ArrayList<DatabaseObject>();
			while (rs.next()) {
				DatabaseObject object = new DatabaseObject();
				Hashtable<String, Object> attribute = new Hashtable<String, Object>();
				attribute.put("category_id", rs.getObject(2) == null ? "" : rs
						.getObject(2));
				attribute.put("id", rs.getObject(3) == null ? "" : rs
						.getObject(3));
				attribute.put("name", rs.getObject(4) == null ? "" : rs
						.getObject(4));
				attribute.put("summary", rs.getObject(5) == null ? "" : rs
						.getObject(5));
				attribute.put("preview_image", rs.getObject(6) == null ? ""
						: rs.getObject(6));
				attribute.put("code", rs.getObject(7) == null ? "" : rs
						.getObject(7));
				attribute.put("avatar_image", rs.getObject(8) == null ? "" : rs
						.getObject(8));
				attribute.put("money", rs.getObject(9) == null ? "" : rs
						.getObject(9));				
				
				object.setAttribute(attribute);
				result.add(object);
			}

		} catch (Exception _e) {
			logger.error("getGameListByCategory: Error executing " + strSql
					+ " >>> " + _e.toString());
		} finally {
			DBPool.releaseConnection(Constant.DB_POOL_MWAP, conn, preStmt, rs);
		}
		return result;

	}
	public ArrayList<DatabaseObject> getContentListByCategoryGroup(BigDecimal catId,
			int page, int rowsPerPage) {
		String strSql = null;
		Connection conn = null;
		PreparedStatement preStmt = null;
		ResultSet rs = null;
		ArrayList<DatabaseObject> result = null;
		try {
			conn = DBPool.getConnection(Constant.DB_POOL_MWAP);
			int startRec = (page - 1) * rowsPerPage + 1;
			int endRec = page * rowsPerPage;
			strSql = "select * from ("
					+ "	select ccat.content_group_id, ctent.content_id, ctent.name,ctent.data, ctent.code, rownum as r from content ctent"
					+ "	left join cnt_cntg_map ccat on ctent.content_id=ccat.content_id"
					+ "	where ccat.content_group_id=?"
					+ ") where r between " + startRec
					+ " and " + endRec;
			// prepare statement
			preStmt = conn.prepareStatement(strSql);
			preStmt.setBigDecimal(1, catId);

			rs = preStmt.executeQuery();
			result = new ArrayList<DatabaseObject>();
			while (rs.next()) {
				DatabaseObject object = new DatabaseObject();
				Hashtable<String, Object> attribute = new Hashtable<String, Object>();
				attribute.put("content_group_id", rs.getObject(1) == null ? "" : rs
						.getObject(1));
				attribute.put("content_id", rs.getObject(2) == null ? "" : rs
						.getObject(2));
				attribute.put("name", rs.getObject(3) == null ? "" : rs
						.getObject(3));
				attribute.put("data", rs.getObject(4) == null ? "" : rs
						.getObject(4));
				attribute.put("code", rs.getObject(5) == null ? "" : rs
						.getObject(5));
				object.setAttribute(attribute);
				result.add(object);
			}

		} catch (Exception _e) {
			logger.error("getGameListByCategory: Error executing " + strSql
					+ " >>> " + _e.toString());
		} finally {
			DBPool.releaseConnection(Constant.DB_POOL_MWAP, conn, preStmt, rs);
		}
		return result;

	}
	public ArrayList<DatabaseObject> getListWeather() {
		String strSql = null;
		Connection conn = null;
		PreparedStatement preStmt = null;
		ResultSet rs = null;
		ArrayList<DatabaseObject> result = null;
		try {
			conn = DBPool.getConnection(Constant.DB_POOL_MWAP);
			//int startRec = (page - 1) * rowsPerPage + 1;
			//int endRec = page * rowsPerPage;
			strSql = "SELECT   a.id, a.code, a.alias, a.name, a.content,"
					+ "TO_CHAR (a.created, 'dd/MM/yyyy hh24:mi:ss') created,"
					+ "TO_CHAR (a.modified, 'dd/MM/yyyy hh24:mi:ss') modified "
					+ "FROM weather_forecasts a ORDER BY a.code ASC ";
	                //+ " where rownum between " + startRec+ " and "+endRec+" ";
			//prepare statement
			System.out.println(strSql);
			preStmt = conn.prepareStatement(strSql);
			rs = preStmt.executeQuery();
			result = new ArrayList<DatabaseObject>();
			while (rs.next()) {
				DatabaseObject object = new DatabaseObject();
				Hashtable<String, Object> attribute = new Hashtable<String, Object>();
				attribute.put("id", rs.getObject(1) == null ? "" : rs
						.getObject(1));
				attribute.put("code", rs.getObject(2) == null ? "" : rs
						.getObject(2));
				attribute.put("alias", rs.getObject(3) == null ? "" : rs
						.getObject(3));
				attribute.put("name", rs.getObject(4) == null ? "" : rs
						.getObject(4));
				attribute.put("content", rs.getObject(5) == null ? ""
						: rs.getObject(5));
				attribute.put("created", rs.getObject(6) == null ? "" : rs
						.getObject(6));
				attribute.put("modified", rs.getObject(7) == null ? "" : rs
						.getObject(7));

				object.setAttribute(attribute);
				result.add(object);
			}

		} catch (Exception _e) {
			logger.error("getListWeather: Error executing " + strSql
					+ " >>> " + _e.toString());
		} finally {
			DBPool.releaseConnection(Constant.DB_POOL_MWAP, conn, preStmt, rs);
		}
		return result;
	}
	public ArrayList<DatabaseObject> getListLotteryBrand(BigDecimal sId) {
		String strSql = null;
		Connection conn = null;
		PreparedStatement preStmt = null;
		ResultSet rs = null;
		ArrayList<DatabaseObject> result = null;
		try {
			conn = DBPool.getConnection(Constant.DB_POOL_MWAP);
			strSql = "SELECT   a.lottery_result_id lottery_result_id, b.NAME name, a.lottery_type_id lottery_type_id, a.VALUE value," +
					"TO_CHAR (a.created, 'dd/MM/yyyy hh24:mi:ss') created," +
					"TO_CHAR (a.modified, 'dd/MM/yyyy hh24:mi:ss') modified " +
					"FROM lottery_result a, lottery_brand b " +
					"WHERE a.lottery_brand_id = b.lottery_brand_id " +
					"AND a.created >= TRUNC (SYSDATE - 1) AND a.lottery_type_id =?" +
					"ORDER BY a.created DESC"; 
			// prepare statement
			System.out.println(strSql);
			preStmt = conn.prepareStatement(strSql);
			preStmt.setBigDecimal(1, sId);

			rs = preStmt.executeQuery();
			result = new ArrayList<DatabaseObject>();
			while (rs.next()) {
				DatabaseObject object = new DatabaseObject();
				Hashtable<String, Object> attribute = new Hashtable<String, Object>();
				attribute.put("lottery_result_id", rs.getObject(1) == null ? "" : rs
						.getObject(1));
				attribute.put("name", rs.getObject(2) == null ? "" : rs
						.getObject(2));
				attribute.put("lottery_type_id", rs.getObject(3) == null ? "" : rs
						.getObject(3));
				attribute.put("value", rs.getObject(4) == null ? "" : rs
						.getObject(4));
				attribute.put("created", rs.getObject(5) == null ? "" : rs
						.getObject(5));
				attribute.put("modified", rs.getObject(6) == null ? "" : rs
						.getObject(6));
				object.setAttribute(attribute);
				result.add(object);
			}
		} catch (Exception _e) {
			logger.error("getLottery Result: Error executing " + strSql
					+ " >>> " + _e.toString());
		} finally {
			DBPool.releaseConnection(Constant.DB_POOL_MWAP, conn, preStmt, rs);
		}
		return result;

	}
	public ArrayList<DatabaseObject> getListLotteryType() {
		String strSql = null;
		Connection conn = null;
		PreparedStatement preStmt = null;
		ResultSet rs = null;
		ArrayList<DatabaseObject> result = null;
		try {
			conn = DBPool.getConnection(Constant.DB_POOL_MWAP);
			//int startRec = (page - 1) * rowsPerPage + 1;
			//int endRec = page * rowsPerPage;
			strSql = "SELECT   a.lottery_type_id, a.code, a.name,"
					+ "TO_CHAR (a.created, 'dd/MM/yyyy hh24:mi:ss') created,"
					+ "TO_CHAR (a.modified, 'dd/MM/yyyy hh24:mi:ss') modified,"
					+ "status FROM lottery_type a " 
					+ "WHERE a.lottery_type_id NOT IN ('45')" 
					+ "ORDER BY a.name ASC ";//   WHERE a.lottery_type_id NOT IN ('45')
	                //+ " where rownum between " + startRec+ " and "+endRec+" ";
			//prepare statement
			System.out.println(strSql);
			preStmt = conn.prepareStatement(strSql);

			rs = preStmt.executeQuery();
			result = new ArrayList<DatabaseObject>();
			while (rs.next()) {
				DatabaseObject object = new DatabaseObject();
				Hashtable<String, Object> attribute = new Hashtable<String, Object>();
				attribute.put("lottery_type_id", rs.getObject(1) == null ? "" : rs
						.getObject(1));
				attribute.put("code", rs.getObject(2) == null ? "" : rs
						.getObject(2));
				attribute.put("name", rs.getObject(3) == null ? "" : rs
						.getObject(3));
				attribute.put("created", rs.getObject(4) == null ? "" : rs
						.getObject(4));
				attribute.put("modified", rs.getObject(5) == null ? ""
						: rs.getObject(5));
				attribute.put("status", rs.getObject(6) == null ? "" : rs
						.getObject(6));
				object.setAttribute(attribute);
				result.add(object);
			}

		} catch (Exception _e) {
			logger.error("getListType: Error executing " + strSql
					+ " >>> " + _e.toString());
		} finally {
			DBPool.releaseConnection(Constant.DB_POOL_MWAP, conn, preStmt, rs);
		}
		return result;
	}
	public ArrayList<DatabaseObject> getContentListByType(BigDecimal typeId,
			int page, int rowsPerPage) {
		String strSql = null;
		Connection conn = null;
		PreparedStatement preStmt = null;
		ResultSet rs = null;
		ArrayList<DatabaseObject> result = null;
		try {
			conn = DBPool.getConnection(Constant.DB_POOL_MWAP);
			int startRec = (page - 1) * rowsPerPage + 1;
			int endRec = page * rowsPerPage;
			strSql = " SELECT   priority, category_id, ID, NAME, summarize, preview_image, code, avatar_image, money " +
	         		 " FROM (SELECT   ctent.priority priority, ccat.category_id category_id, " +
	                                " ctent.ID ID, ctent.NAME NAME, ctent.summarize summarize, " +
	                                " ctent.preview_image preview_image, ctent.code code, " +
	                                " ctent.avatar_image avatar_image, ctent.money money " +
	                        " FROM vnt_media_content ctent, content_category_map ccat , content_instance a, content_format b" +
	                        " WHERE ctent.ID = ccat.content_id(+) AND ctent.ID = a.content_id(+) AND a.format_id = b.ID(+)" +	
	                        " AND ctent.type_id = ? " +
	                        " AND ctent.status = 1  " + //AND b.code = 'JAR' " +
	                        " AND ctent.avatar_image IS NOT NULL " +
	                        " AND ctent.preview_image IS NOT NULL " +
	                        " ORDER BY ctent.priority ASC, ctent.created DESC)" +
	                        " WHERE rownum between " + startRec+ " and "+endRec+" ";

			//prepare statement
			System.out.println(strSql);
			preStmt = conn.prepareStatement(strSql);
			preStmt.setBigDecimal(1, typeId);

			rs = preStmt.executeQuery();
			result = new ArrayList<DatabaseObject>();
			while (rs.next()) {
				DatabaseObject object = new DatabaseObject();
				Hashtable<String, Object> attribute = new Hashtable<String, Object>();
				attribute.put("category_id", rs.getObject(2) == null ? "" : rs
						.getObject(2));
				attribute.put("id", rs.getObject(3) == null ? "" : rs
						.getObject(3));
				attribute.put("name", rs.getObject(4) == null ? "" : rs
						.getObject(4));
				attribute.put("summary", rs.getObject(5) == null ? "" : rs
						.getObject(5));
				attribute.put("preview_image", rs.getObject(6) == null ? ""
						: rs.getObject(6));
				attribute.put("code", rs.getObject(7) == null ? "" : rs
						.getObject(7));
				attribute.put("avatar_image", rs.getObject(8) == null ? "" : rs
						.getObject(8));
				attribute.put("money", rs.getObject(9) == null ? "" : rs
						.getObject(9));
				object.setAttribute(attribute);
				result.add(object);
			}

		} catch (Exception _e) {
			logger.error("getGameListByCategory: Error executing " + strSql
					+ " >>> " + _e.toString());
		} finally {
			DBPool.releaseConnection(Constant.DB_POOL_MWAP, conn, preStmt, rs);
		}
		return result;

	}
	public ArrayList<DatabaseObject> getContentListByTypeVn(BigDecimal typeId,
			int page, int rowsPerPage) {
		String strSql = null;
		Connection conn = null;
		PreparedStatement preStmt = null;
		ResultSet rs = null;
		ArrayList<DatabaseObject> result = null;
		try {
			conn = DBPool.getConnection(Constant.DB_POOL_MWAP);
			int startRec = (page - 1) * rowsPerPage + 1;
			int endRec = page * rowsPerPage;
			strSql = " SELECT   content_group_id,content_id, NAME, code " +
	         		 " FROM (SELECT   ccat.content_group_id content_group_id, " +
	                                " ctent.content_id content_id, ctent.NAME NAME, " +
	                                " ctent.code code " +
	                        " FROM content ctent, cnt_cntg_map ccat " +
	                        " WHERE ctent.content_id = ccat.content_id(+) " +	
	                        " AND ctent.content_type_id = ? " +
	                        " ORDER BY ctent.created DESC)" +
	                        " WHERE rownum between " + startRec+ " and "+endRec+" ";

			//prepare statement
			System.out.println(strSql);
			preStmt = conn.prepareStatement(strSql);
			preStmt.setBigDecimal(1, typeId);

			rs = preStmt.executeQuery();
			result = new ArrayList<DatabaseObject>();
			while (rs.next()) {
				DatabaseObject object = new DatabaseObject();
				Hashtable<String, Object> attribute = new Hashtable<String, Object>();
				attribute.put("content_group_id", rs.getObject(1) == null ? "" : rs
						.getObject(1));
				attribute.put("content_id", rs.getObject(2) == null ? "" : rs
						.getObject(2));
				attribute.put("name", rs.getObject(3) == null ? "" : rs
						.getObject(3));
				object.setAttribute(attribute);
				result.add(object);
			}

		} catch (Exception _e) {
			logger.error("getGameListByCategoryVn: Error executing " + strSql
					+ " >>> " + _e.toString());
		} finally {
			DBPool.releaseConnection(Constant.DB_POOL_MWAP, conn, preStmt, rs);
		}
		return result;

	}
	public ArrayList<DatabaseObject> getContentHOTListByType(BigDecimal typeId,
			int page, int rowsPerPage) {
		String strSql = null;
		Connection conn = null;
		PreparedStatement preStmt = null;
		ResultSet rs = null;
		ArrayList<DatabaseObject> result = null;
		try {
			conn = DBPool.getConnection(Constant.DB_POOL_MWAP);
			// int startRec = (page-1) * rowsPerPage + 1;
			// int endRec = page * rowsPerPage;
			strSql = "SELECT * FROM ("
					+ " SELECT ccat.priority, ccat.category_id, ctent.id, ctent.name, ctent.SUMMARIZE, ctent.PREVIEW_IMAGE, ctent.code, ctent.AVATAR_IMAGE, ctent.money money from vnt_media_content ctent"
					+ " left join CONTENT_CATEGORY_MAP ccat on ctent.id=ccat.content_id WHERE ctent.status=1 and ctent.type_id=?"
					+ " AND ctent.preview_image IS NOT NULL"
					+ " AND ctent.avatar_image IS NOT NULL"
					+ " AND instr(ctent.preview_image,'PROMOTION_GIRL') <=0"
					+ " AND instr(ctent.preview_image,'_PG') <=0"
					+ " ORDER BY dbms_random.value)WHERE rownum <= 5";

			// prepare statement
			preStmt = conn.prepareStatement(strSql);
			preStmt.setBigDecimal(1, typeId);

			rs = preStmt.executeQuery();
			result = new ArrayList<DatabaseObject>();
			while (rs.next()) {
				DatabaseObject object = new DatabaseObject();
				Hashtable<String, Object> attribute = new Hashtable<String, Object>();
				attribute.put("category_id", rs.getObject(2) == null ? "" : rs
						.getObject(2));
				attribute.put("id", rs.getObject(3) == null ? "" : rs
						.getObject(3));
				attribute.put("name", rs.getObject(4) == null ? "" : rs
						.getObject(4));
				attribute.put("summary", rs.getObject(5) == null ? "" : rs
						.getObject(5));
				attribute.put("preview_image", rs.getObject(6) == null ? ""
						: rs.getObject(6));
				attribute.put("code", rs.getObject(7) == null ? "" : rs
						.getObject(7));
				attribute.put("avatar_image", rs.getObject(8) == null ? "" : rs
						.getObject(8));
				attribute.put("money", rs.getObject(9) == null ? "" : rs
						.getObject(9)); 
				
				object.setAttribute(attribute);
				result.add(object);
			}

		} catch (Exception _e) {
			logger.error("getGameListByCategory: Error executing " + strSql
					+ " >>> " + _e.toString());
		} finally {
			DBPool.releaseConnection(Constant.DB_POOL_MWAP, conn, preStmt, rs);
		}
		return result;
	}
	public ArrayList<DatabaseObject> getContentHOTListByTypeVn(BigDecimal typeId,
			int page, int rowsPerPage) {
		String strSql = null;
		Connection conn = null;
		PreparedStatement preStmt = null;
		ResultSet rs = null;
		ArrayList<DatabaseObject> result = null;
		try {
			conn = DBPool.getConnection(Constant.DB_POOL_MWAP);
			// int startRec = (page-1) * rowsPerPage + 1;
			// int endRec = page * rowsPerPage;
			strSql = "SELECT * FROM ("
					+ " SELECT ccat.content_group_id, ctent.content_id, ctent.name,ctent.code from content ctent"
					+ " left join cnt_cntg_map ccat on ctent.content_id=ccat.content_id WHERE ctent.content_type_id=?"
					+ " ORDER BY dbms_random.value)WHERE rownum <= 5";

			// prepare statement
			preStmt = conn.prepareStatement(strSql);
			preStmt.setBigDecimal(1, typeId);

			rs = preStmt.executeQuery();
			result = new ArrayList<DatabaseObject>();
			while (rs.next()) {
				DatabaseObject object = new DatabaseObject();
				Hashtable<String, Object> attribute = new Hashtable<String, Object>();
				attribute.put("content_group_id", rs.getObject(1) == null ? "" : rs
						.getObject(1));
				attribute.put("content_id", rs.getObject(2) == null ? "" : rs
						.getObject(2));
				attribute.put("name", rs.getObject(3) == null ? "" : rs
						.getObject(3));
				attribute.put("code", rs.getObject(4) == null ? "" : rs
						.getObject(4));
				object.setAttribute(attribute);
				result.add(object);
			}

		} catch (Exception _e) {
			logger.error("getGameListByCategoryVn: Error executing " + strSql
					+ " >>> " + _e.toString());
		} finally {
			DBPool.releaseConnection(Constant.DB_POOL_MWAP, conn, preStmt, rs);
		}
		return result;
	}
	public int testLogin(String user, String pass) {
		String strSql = null;
		Connection conn = null;
		PreparedStatement preStmt = null;
		ResultSet rs = null;
		int result = 0;
		try {
			conn = DBPool.getConnection(Constant.DB_POOL_MWAP);
			strSql = "select * from USER_INFO where user_name = '"+user+"' AND password = '"+pass+"'";
			// prepare statement
			preStmt = conn.prepareStatement(strSql);
			rs = preStmt.executeQuery();
			if (rs.next()) {
				result = rs.getInt(1);
			}
		} catch (Exception _e) {
			logger.error("test username and password: Error executing " + strSql
					+ " >>> " + _e.toString());
		} finally {
			DBPool.releaseConnection(Constant.DB_POOL_MWAP, conn, preStmt, rs);
		}
		return result;
	}

	public int viewCount(BigDecimal viewId) {
		String strSql = null;
		Connection conn = null;
		PreparedStatement preStmt = null;
		ResultSet rs = null;
		int result = 0;
		try {
			conn = DBPool.getConnection(Constant.DB_POOL_MWAP);
			strSql = "select view_count from vnt_media.content_view where content_id = "+viewId;
			// prepare statement
			preStmt = conn.prepareStatement(strSql);
			rs = preStmt.executeQuery();
			if (rs.next()) {
				result = rs.getInt(1);
			}

		} catch (Exception _e) {
			logger.error("view count: Error executing " + strSql
					+ " >>> " + _e.toString());
		} finally {
			DBPool.releaseConnection(Constant.DB_POOL_MWAP, conn, preStmt, rs);
		}
		return result;
	}

	
	public int viewDownloadCount(BigDecimal viewId) {
		String strSql = null;
		Connection conn = null;
		PreparedStatement preStmt = null;
		ResultSet rs = null;
		int result = 0;
		try {
			conn = DBPool.getConnection(Constant.DB_POOL_MWAP);
			strSql = "select download_count from vnt_media.content_view where content_id = "+viewId;
			// prepare statement
			preStmt = conn.prepareStatement(strSql);
			rs = preStmt.executeQuery();
			if (rs.next()) {
				result = rs.getInt(1);
			}

		} catch (Exception _e) {
			logger.error("view count: Error executing " + strSql
					+ " >>> " + _e.toString());
		} finally {
			DBPool.releaseConnection(Constant.DB_POOL_MWAP, conn, preStmt, rs);
		}
		return result;
	}

	public DatabaseObject getWeather(BigDecimal id) {
		ArrayList<DatabaseObject> games = findByDynamicWhere(
				Constant.DB_POOL_MWAP, "weather_forecasts", new String[] { "id",
						"code", "alias", "name", "content",
						"created", "modified" },
				new String[] { null, null, null, null, null, null, null,
						}, "id=?",
				new Object[] { id }, null);

		if (games != null && !games.isEmpty())
			return games.get(0);
		return null;
	}

	public DatabaseObject getContent(BigDecimal id) {
		ArrayList<DatabaseObject> games = findByDynamicWhere(
				Constant.DB_POOL_MWAP, "vnt_media_content", new String[] { "id",
						"type_id", "url", "name", "summarize", "detail",
						"preview_image", "avatar_image", "avatar_image_w",
						"avatar_image_h", "status", "code", "money" },
				new String[] { null, null, null, null, null, null, null, null,
						null, null, null, null, null }, "id=?",
				new Object[] { id }, null);

		if (games != null && !games.isEmpty())
			return games.get(0);
		return null;
	}
	public DatabaseObject getContentVn(BigDecimal id) {
		ArrayList<DatabaseObject> games = findByDynamicWhere(
				Constant.DB_POOL_MWAP, "content", new String[] { "content_id",
						"content_type_id", "code", "name", "data", "created",
						"modified", "alias"},
				new String[] { null, null, null, null, null, null, null, null},"content_id=?",
				new Object[] { id }, null);

		if (games != null && !games.isEmpty())
			return games.get(0);
		return null;
	}
	public DatabaseObject getContentview(BigDecimal id) {
		ArrayList<DatabaseObject> games = findByDynamicWhere(
				Constant.DB_POOL_MWAP, "vnt_media.content_view",
				new String[] { "viewer_id", "content_id", "view_count",
						"last_view", "download_count", "last_download", "rank",
						"last_rank" }, new String[] { null, null, null, null,
						null, null, null, null }, "content_id=?",
				new Object[] { id }, null);

		if (games != null && !games.isEmpty())
			return games.get(0);
		return null;
	}
	public int checkCount(BigDecimal sId) {
		String strSql = null;
		Connection conn = null;
		PreparedStatement preStmt = null;
		ResultSet rs = null;
		int result = 0;
		try {
			conn = DBPool.getConnection(Constant.DB_POOL_MWAP);
			strSql = "select count(*) from vnt_media.content_view where content_id = "+sId;
			// prepare statement
			preStmt = conn.prepareStatement(strSql);
			rs = preStmt.executeQuery();
			if (rs.next()) {
				result = rs.getInt(1);
			}

		} catch (Exception _e) {
			logger.error("view count: Error executing " + strSql
					+ " >>> " + _e.toString());
		} finally {
			DBPool.releaseConnection(Constant.DB_POOL_MWAP, conn, preStmt, rs);
		}
		return result;
	}
	public boolean insertUserInfo(String user, String pass) {
		DatabaseDAO databaseDAO = new DatabaseDAO();
		String sql = "insert into user_info(id,user_name,password) values(user_seq.NEXTVAL,'"+user+"','"+pass+"')";
		System.out.println("Insert: "+sql);
		return databaseDAO.executeSQLStatement(Constant.DB_POOL_MWAP, sql);
	}
	public boolean insertContentView(String id) {
		DatabaseDAO databaseDAO = new DatabaseDAO();
		String sql = "insert into vnt_media.content_view(viewer_id,content_id,view_count,last_view) values(seq_content_view.NEXTVAL,'"+id+"',1,sysdate)";
		System.out.println("sql"+sql);
		return databaseDAO.executeSQLStatement(Constant.DB_POOL_MWAP, sql);
	}
	
	public boolean updateContentView(String id, int iCount) {
		DatabaseDAO databaseDAO = new DatabaseDAO();
		iCount++;
		String sql = "update vnt_media.content_view set view_count = "+iCount+" where content_id ="+id;
		return databaseDAO.executeSQLStatement(Constant.DB_POOL_MWAP, sql);
	}
	
	public boolean insertContentDown(String id) {
		DatabaseDAO databaseDAO = new DatabaseDAO();
		String sql = "insert into vnt_media.content_view(viewer_id,content_id,download_count,last_view) values(seq_Content_view.NEXTVAL,'"+id+"',1,sysdate)";
		return databaseDAO.executeSQLStatement(Constant.DB_POOL_MWAP, sql);
	}
	public boolean updateContentDownload(String id, int iCountDownload) {
		DatabaseDAO databaseDAO = new DatabaseDAO();
		iCountDownload++;
		String sql = "update vnt_media.content_view set download_count = "+iCountDownload+" where content_id ="+id;
		return databaseDAO.executeSQLStatement(Constant.DB_POOL_MWAP, sql);
	}
	//register and login
	public static void main(String[] args) {
		DBContentDAO contentDAO = new DBContentDAO();
		ArrayList<DatabaseObject> gameList = contentDAO.getGameListByCategory(new BigDecimal("203"), 1, 10);
		for (DatabaseObject game : gameList) {
			System.out.println(game.getAttribute().get("name") + "\n\t"
					+ game.getAttribute().get("summary"));
		}

	}

}
